﻿//-----------------------------------------------------------------------
// <copyright file="SQLDbAccount.cs" >
//     All rights reserved.
//     THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY 
//     KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
//     IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
//     PARTICULAR PURPOSE.
// </copyright>
// <author>Marais van Zyl</author>
// <email>marais@projexis.co.za</email>
// <CreatedDate>2010/01/26</CreatedDate>
//-----------------------------------------------------------------------
namespace Accountability4net.MSSQLDbProvider
{
    using System;
    using System.Collections.Generic;

    /// <summary>
    /// Defines the an account object to retrieve account details using the SQLDBProvider
    /// </summary>
    public class SQLDbAccount : BaseAccount
    {
        /// <summary>
        /// the provider currently in use
        /// </summary>
        private SQLDbProvider provider_;

        /// <summary>
        /// Initializes a new instance of the SQLDbAccount class
        /// </summary>
        /// <param name="provider">the sql provider to be used with this object</param>
        public SQLDbAccount(SQLDbProvider provider)
            : this()
        {
            this.provider_ = provider;
        }

        /// <summary>
        /// Initializes a new instance of the SQLDbAccount class
        /// </summary>
        protected SQLDbAccount()
            : base()
        {
        }

        /// <summary>
        /// Creates a new account and saves it to the system
        /// </summary>
        /// <param name="provider">the provider to use when creating the account</param>
        /// <param name="number">the number of the account</param>
        /// <param name="type">the type of the account</param>
        /// <returns>the newly created account</returns>
        public static SQLDbAccount Create(SQLDbProvider provider, int number, AccountType type)
        {
            SQLDbAccount account = new SQLDbAccount(provider);
            account.Number = number;
            account.Type = type;
            string sql = "INSERT INTO [account] ([Number], [Type]) VALUES (@number, @type)";
            using (var command = provider.CreateCommand(sql))
            {
                command.Parameters.AddWithValue("@number", number);
                command.Parameters.AddWithValue("@type", (byte)type);
                int rows = command.ExecuteNonQuery();
            }

            account.WirePropertyChanged();
            return account;
        }

        /// <summary>
        /// Retrieves an account using the SQLDBProvider specified
        /// </summary>
        /// <param name="provider">the provider to use to retrieve the account details</param>
        /// <param name="number">the number of the account to retrieve</param>
        /// <returns>the details of the account retrieved</returns>
        public static IAccount Retrieve(SQLDbProvider provider, int number)
        {
            SQLDbAccount account = new SQLDbAccount(provider);
            string sql = "SELECT * FROM [Account] WITH (nolock) WHERE [Number] = @number";
            using (var command = provider.CreateCommand(sql))
            {
                command.Parameters.AddWithValue("@number", number);
                using (var reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        account.Number = reader.GetInt32(0);
                        account.Type = (AccountType)Enum.ToObject(typeof(AccountType), reader.GetByte(1));
                        account.Balance = reader.GetDecimal(2);
                        account.Name = provider.GetString(reader, "Name");
                        account.Description = provider.GetString(reader, "Description");
                        account.WirePropertyChanged();
                    }
                    else
                    {
                        return null;
                    }

                    reader.Close();
                }
            }

            return account;
        }

        /// <summary>
        /// Retrieves the records from a row number to 
        /// </summary>
        /// <param name="provider">the providers being used</param>
        /// <param name="from">the lowered row number limit</param>
        /// <param name="limit">the upper row number limit</param>
        /// <returns>the list of accounts found</returns>
        public static IList<IAccount> Retrieve(SQLDbProvider provider, int from, int limit)
        {
            var result = new List<IAccount>();
            string sql = "SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY Number) as row FROM [Account]) a WHERE a.Number >=  @from AND a.row <= @limit";
            using (var command = provider.CreateCommand(sql))
            {
                command.Parameters.AddWithValue("@from", from);
                command.Parameters.AddWithValue("@limit", limit);
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        SQLDbAccount account = new SQLDbAccount(provider);
                        account.Number = reader.GetInt32(0);
                        account.Type = (AccountType)Enum.ToObject(typeof(AccountType), reader.GetByte(1));
                        account.Balance = reader.GetDecimal(2);
                        account.Name = provider.GetString(reader, "Name");
                        account.Description = provider.GetString(reader, "Description");
                        account.WirePropertyChanged();
                        result.Add(account);
                    }

                    reader.Close();
                }
            }

            return result;
        }

        /// <summary>
        /// Impliments the PropertyChanged event of the class
        /// </summary>
        public void WirePropertyChanged()
        {
            PropertyChanged += this.SQLAdoDbAccount_PropertyChanged;
        }

        /// <summary>
        /// Updates any properties that has changed
        /// </summary>
        /// <param name="sender">the sender of the event</param>
        /// <param name="e">the <see cref="System.ComponentModel.PropertyChangedEventArgs"/> arguments</param>
        private void SQLAdoDbAccount_PropertyChanged(object sender, System.ComponentModel.PropertyChangedEventArgs e)
        {
            string sql = "UPDATE [account]"
                + " SET [Balance] = @balance"
                + ", [Name] = @name"
                + ", [Description] = @description"
                + " WHERE [Number] = @number";
            using (var command = this.provider_.CreateCommand(sql))
            {
                command.Parameters.AddWithValue("@number", this.Number);
                command.Parameters.AddWithValue("@balance", this.Balance);
                command.Parameters.AddWithValue("@name", this.provider_.GetDbString(this.Name));
                command.Parameters.AddWithValue("@description", this.provider_.GetDbString(this.Description));
                int rows = command.ExecuteNonQuery();
            }
        }
    }
}
